<?php
/**
 * Product: ASW.Database.
 * Date: 2023-09-10
 * Time: 15:12
 */

namespace ASW\Database;


abstract class SqlAdapter
{
    /**
     * @template Type
     * @param class-string<Type> $adapterClassName
     * @param DbConnection $connection
     * @return Type
     */
    public static function getInstance(string $adapterClassName, DbConnection $connection)
    {
        if (empty($adapterClassName)) throw new \Exception("必须指定 SqlAdapter 类名");
        if (!class_exists($adapterClassName)) throw new \Exception("指定的 SqlAdapter 类 $adapterClassName 不存在");

        return new $adapterClassName($connection);
    }

    protected string $_tableName = '';
    protected string $_fields    = '';
    protected string $_join      = '';
    protected string $_where     = '';
    protected string $_orderBy   = '';
    protected string $_groupBy   = '';
    protected int    $_pageSize  = 0;
    protected int    $_pageIndex = 0;
    protected array  $_params    = [];

    public function __construct(private DbConnection $dbConnection)
    {
    }

    public function setConnection(DbConnection $dbConnection, bool $autoCloseOld = true): void
    {
        if ($autoCloseOld) {
            $this->dbConnection?->close();
        }
        $this->dbConnection = $dbConnection;
    }

    public function closeDbConnection(): void
    {
        $this->dbConnection->close();
    }

    public function query(string $sql): \PDOStatement|false
    {
        return $this->dbConnection->query($sql, $this->_params);
    }

    /**
     * 启用事务
     */
    public function transBegin(): bool
    {
        return $this->dbConnection->transBegin();
    }

    /**
     * 提交事务
     */
    public function transCommit(): bool
    {
        return $this->dbConnection->transCommit();
    }

    /**
     * 回滚事务
     */
    public function transRollBack(): bool
    {
        return $this->dbConnection->transRollBack();
    }

    /**
     * 最后自增id
     * @return int
     */
    public function getLastInsertId(): int
    {
        return $this->dbConnection->getLastInsertId();
    }


    /**
     * @param string ...$propertyNames
     *
     * @return static
     */
    public function reset(string ...$propertyNames): static
    {
        $allPropertyMap = [
            'fields'    => '',
            'join'      => '',
            'where'     => '',
            'orderBy'   => '',
            'groupBy'   => '',
            'pageSize'  => 0,
            'pageIndex' => 0,
            'params'    => [],
        ];

        $resetPropertyNameArray = empty($propertyNames) ? array_keys($allPropertyMap) : $propertyNames;
        foreach ($resetPropertyNameArray as $propertyName) {
            if (!array_key_exists($propertyName, $allPropertyMap)) continue;
            $propertyPrivateName        = "_$propertyName";
            $this->$propertyPrivateName = $allPropertyMap[$propertyName];
        }
        return $this;
    }

    /**
     * @param string $tableName
     *
     * @return static
     */
    public function table(string $tableName): static
    {
        $this->_tableName = trim($tableName);
        return $this;
    }

    /**
     * @param string|null $fields
     * @param bool $append
     *
     * @return static
     */
    public function fields(?string $fields, bool $append = true): static
    {
        $fields = trim($fields);
        if (!empty($fields)) {
            $this->_fields = $append && !empty(trim($this->_fields)) ? "$this->_fields, $fields" : $fields;
        }
        return $this;
    }

    /**
     * @param string|null $where
     * @param bool $append
     * @param string $appendWith
     *
     * @return static
     */
    public function where(?string $where, bool $append = true, string $appendWith = 'AND'): static
    {
        $where = trim($where);
        if (!empty($where)) {
            if (!$append || empty(trim($this->_where))) {
                $this->_where = $where;
            } else {
                $appendWith   = trim($appendWith);
                $this->_where = "$this->_where $appendWith $where";
            }
        }
        return $this;
    }

    /**
     * @param string|null $join
     * @param bool $append
     *
     * @return static
     */
    public function join(?string $join, bool $append = true): static
    {
        $join = trim($join);
        if (!empty($join)) $this->_join = $append ? "$this->_join $join" : $join;
        return $this;
    }

    /**
     * @param string $orderBy
     *
     * @return static
     */
    public function orderBy(string $orderBy = ''): static
    {
        $this->_orderBy = trim($orderBy);
        return $this;
    }

    /**
     * @param string $groupBy
     *
     * @return static
     */
    public function groupBy(string $groupBy = ''): static
    {
        $this->_groupBy = trim($groupBy);
        return $this;
    }

    /**
     * @param int $pageSize
     * @param int $pageIndex
     *
     * @return static
     */
    public function page(int $pageSize, int $pageIndex = 1): static
    {
        if ($pageIndex <= 0) $pageIndex = 1;
        $this->_pageSize  = $pageSize;
        $this->_pageIndex = $pageIndex;
        return $this;
    }

    /**
     * @param string $paramKey
     * @param null $paramValue
     *
     * @return static
     */
    public function param(string $paramKey, $paramValue = null): static
    {
        $paramKey = trim($paramKey);
        if (!empty($paramKey)) {
            $this->_params[$paramKey] = $paramValue;
        }
        return $this;
    }

    /**
     * @param string $paramKey
     * @return static
     */
    public function unParam(string $paramKey): static
    {
        $paramKey = trim($paramKey);
        if (!empty($paramKey)) {
            unset($this->_params[$paramKey]);
        }
        return $this;
    }

    /**
     * @param array $params
     *
     * @return static
     */
    public function params(array $params): static
    {
        foreach ($params as $k => $v) $this->param($k, $v);
        return $this;
    }

    /**
     * 构造 INSERT SQL 语句
     *
     * @param array $insertData
     * @param bool $ignoreError
     * @param string $onDuplicateKey
     *
     * @return string
     */
    protected abstract function generateInsertSql(array $insertData, bool $ignoreError, string $onDuplicateKey): string;

    /**
     * 构造 UPSERT SQL 语句
     *
     * @param array $insertData
     * @param array $updateData
     *
     * @return string
     */
    protected abstract function generateUpsertSql(array $insertData, array $updateData): string;

    /**
     * 构造 批量 INSERT SQL 语句
     *
     * @param array $insertDataList
     * @param bool $ignoreError
     * @param string $onDuplicateKey
     *
     * @return string
     */
    protected abstract function generateMultiInsertSql(array $insertDataList, bool $ignoreError, string $onDuplicateKey): string;

    /**
     * 构造 UPDATE SQL 语句
     *
     * @param array $updateData
     *
     * @return string
     */
    protected abstract function generateUpdateSql(array $updateData): string;

    /**
     * 构造 SELECT SQL 语句
     * @return string
     */
    protected abstract function generateSelectSql(): string;

    /**
     * 构造 DELETE SQL 语句
     *
     * @param array $otherTableNames
     *
     * @return string
     */
    protected abstract function generateDeleteSql(array $otherTableNames = []): string;


    /**
     * @param array $insertData
     * @param array $updateData
     * @param string $uniqueKey 冲突唯一键, SQLite中使用
     *
     * @return int|false
     */
    public function upsert(array $insertData, array $updateData = [], string $uniqueKey = ''): int|false
    {
        if (empty($updateData)) $updateData = $insertData;
        $sql = $this->generateUpsertSql($insertData, $updateData, $uniqueKey);
        if (empty($sql)) return 0;
        if (false === $queryStatement = $this->query($sql)) return false;
        return $queryStatement->rowCount();
    }

    /**
     * @param array $insertData
     * @param string $onDuplicateKey
     *
     * @return int|false
     */
    public function insert(array $insertData, string $onDuplicateKey = ''): int|false
    {
        $sql = $this->generateInsertSql($insertData, false, $onDuplicateKey);
        if (empty($sql)) return 0;
        if (false === $queryStatement = $this->query($sql)) return false;
        return $queryStatement->rowCount();
    }

    /**
     * Insert 操作
     *
     * @param array $insertData
     * @param bool $ignoreError 主键或唯一索引冲突时忽略插入
     *
     * @return int|false
     */
    public function insertGetId(array $insertData, bool $ignoreError = false): int|false
    {
        $sql = $this->generateInsertSql($insertData, $ignoreError, '');
        if (empty($sql)) return 0;
        if (false === $queryStatement = $this->query($sql)) return false;
        if ($queryStatement->rowCount() === 0) return 0;
        return $this->getLastInsertId();
    }

    /**
     * 批量插入操作
     *
     * @param array $insertDataList
     * @param bool $ignoreError
     * @param string $onDuplicateKey
     *
     * @return int|false
     */
    public function multiInsert(array $insertDataList, bool $ignoreError = false, string $onDuplicateKey = ''): int|false
    {
        $sql = $this->generateMultiInsertSql($insertDataList, $ignoreError, $onDuplicateKey);
        if (empty($sql)) return 0;
        if (false === $queryStatement = $this->query($sql)) return false;
        return $queryStatement->rowCount();
    }

    /**
     * Delete 操作
     *
     * @param string $where
     * @param string[] $otherTableNames
     *
     * @return int|false
     */
    public function delete(string $where = '', array $otherTableNames = []): int|false
    {
        if (!empty($where)) $this->where($where);
        $sql = $this->generateDeleteSql($otherTableNames);
        if (empty($sql)) return 0;
        if (false === $queryStatement = $this->query($sql)) return false;
        return $queryStatement->rowCount();
    }

    /**
     * Update 操作
     *
     * @param array $updateData
     *
     * @return int|false
     */
    public function update(array $updateData): int|false
    {
        $sql = $this->generateUpdateSql($updateData);
        if (empty($sql)) return 0;
        if (false === $queryStatement = $this->query($sql)) return false;
        return $queryStatement->rowCount();
    }

    /**
     * 单个字段 Update 操作
     *
     * @param string $fieldName
     * @param int|float|string|null $filedValue
     *
     * @return int|false
     */
    public function updateField(string $fieldName, int|float|string|null $filedValue): int|false
    {
        return $this->update([$fieldName => $filedValue]);
    }

    /**
     * 对单个字段增加指定的 step 值
     * 如果更新为单行, 返回更新后的值, 否则返回更新行数
     *
     * @param string $fieldName
     * @param int|float $step
     *
     * @return int|float|false
     */
    public function incField(string $fieldName, int|float $step = 1): int|float|false
    {
        $updateResult = $this->updateField($fieldName, "::$fieldName + $step");
        if ($updateResult === 1) return $this->getFieldValue($fieldName);
        return $updateResult;
    }

    /**
     * Select 查询
     *
     * @return array[]|false
     */
    public function select(): array|false
    {
        $sql = $this->generateSelectSql();
        if (false === $queryStatement = $this->query($sql)) return false;
        return $queryStatement->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * 查询首条记录, 若不存在返回null, 若报错返回false
     *
     * @return array|null
     */
    public function find(): array|null
    {
        if (false === $dataSet = $this->page(1)->select()) return null;
        if (empty($dataSet)) return null;
        return $dataSet[0];
    }

    /**
     * 取查询结果首条记录的指定字段值， 若记录不存在或查询失败返回默认值
     *
     * @param string $field
     * @param string|int|float|null $default
     *
     * @return string|int|float|null
     */
    public function getFieldValue(string $field, null|string|int|float $default = null): string|int|float|null
    {
        if (null === $row = $this->fields("$field AS __val")->find()) return $default;
        return $row['__val'];
    }

    /**
     * 取查询结果的指定字段值数组
     *
     * @param string $field 作为数组值的字段
     * @param string $keyField 作为数组键名的字段
     *
     * @return array
     */
    public function selectFieldValue(string $field, string $keyField = ''): array
    {
        $queryFields = "$field AS __val";
        if (!empty($keyField)) $queryFields .= ", $keyField AS __key";

        $list = $this->fields($queryFields)->select();
        if (!is_array($list) || empty($list)) return [];

        $valueArray = array_column($list, '__val');
        if (empty($keyField)) return $valueArray;

        $keyArray = array_column($list, '__key');
        return array_combine($keyArray, $valueArray);
    }

    /**
     * 取查询结果以指定字段作为键名的字典
     *
     * @param string $keyField
     *
     * @return array[]
     */
    public function selectDictionary(string $keyField = 'id'): array
    {
        $list = $this->select();
        if (empty($list)) return [];
        $keyArray = array_column($list, $keyField);
        return array_combine($keyArray, $list);
    }

    /**
     * 统计符合指定条件的记录数
     *
     * @param string $where
     *
     * @return int
     */
    public function count(string $where = ''): int
    {
        if (!empty($where)) $this->where($where);
        $val = $this->orderBy('')->groupBy('')->getFieldValue('COUNT(1)', 0);
        return intval($val);
    }

    /**
     * 判断符合指定条件的记录是否存在
     *
     * @param string $where
     *
     * @return bool
     */
    public function exists(string $where = ''): bool
    {
        return $this->where($where)->count() > 0;
    }

    /**
     * 取指定字段总和
     *
     * @param string $field
     * @param string $where
     *
     * @return int|float
     */
    public function sum(string $field, string $where = ''): int|float
    {
        if (!empty($where)) $this->where($where);
        $val = $this->orderBy('')->groupBy('')->getFieldValue("SUM($field)", 0);
        return floatval($val);
    }

    /**
     * 取指定字段平均值
     *
     * @param string $field
     * @param string $where
     *
     * @return int|float
     */
    public function avg(string $field, string $where = ''): int|float
    {
        if (!empty($where)) $this->where($where);
        $val = $this->orderBy('')->groupBy('')->getFieldValue("AVG($field)", 0);
        return floatval($val);
    }

    /**
     * 取指定字段最大值
     *
     * @param string $field
     * @param string $where
     *
     * @return string|int|float|null
     */
    public function max(string $field, string $where = ''): string|int|float|null
    {
        if (!empty($where)) $this->where($where);
        return $this->orderBy('')->groupBy('')->getFieldValue("MAX($field)");
    }

    /**
     * 取指定字段最小值
     *
     * @param string $field
     * @param string $where
     *
     * @return string|int|float|null
     */
    public function min(string $field, string $where = ''): string|int|float|null
    {
        if (!empty($where)) $this->where($where);
        return $this->orderBy('')->groupBy('')->getFieldValue("MIN($field)");
    }
}